SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
 CREATE PROCEDURE [dbo].[DailyReporting_ValComm_Backup]
    @FundID VARCHAR(50) ,
    @NAVDate DATE = NULL ,
    @ReportName VARCHAR(100) = NULL
 AS 
    BEGIN
           
           --REMEMBER TO REMOVE THE SECTION MARKED BY: %%REMOVE  (Just search for %%REMOVE)
           --IT IS A PRICING OVERRIDE FOR 3.31 VALCOM WHERE PRICING
           --DID NOT EXIST IN ARTEMIS YET!!!!!
        SET FMTONLY ON;
        DECLARE @fmtonlyON BIT
        SET @fmtonlyON = 0
        IF ( 1 = 0 ) 
            BEGIN
                SET @fmtonlyON = 1
            END
        SET FMTONLY OFF;
           
           
        SET NOCOUNT ON;
           
           
           --DECLARE @FundID VARCHAR(50)
           -- DECLARE   @NAVDate DATE = NULL
           --SET @FundID = 'FSIC'
           --SET @NAVDate ='6/28/2013'
           
        DECLARE @PriorNAVDATE DATE
        DECLARE @LastValcomExportID INT
        DECLARE @LastValcomDate DATE
        DECLARE @Cash DECIMAL(30, 2)
        DECLARE @netlevamt DECIMAL(30, 2)
        DECLARE @fundlevyld DECIMAL(30, 10)
           
           
           
        DECLARE @ExportID INT
        SET @ExportID = CASE WHEN @NAVDate IS NULL
                             THEN ( SELECT  MAX(ex.ExportID)
                                    FROM    [FSFundModel_VALCOMM].[Reporting].Export ex
                                    WHERE   ex.FundId = @FundID
                                  )
                             ELSE ( SELECT  MAX(ex.ExportID)
                                    FROM    [FSFundModel_VALCOMM].[Reporting].Export ex
                                    WHERE   NAVDate = @NAVDate
                                            AND FundID = @FundID
                                  )
                        END
           
        SET @LastValcomDate = ( SELECT  e.NAVDate
                                FROM    [FSFundModel_VALCOMM].[Reporting].Export e
                                WHERE   e.FundId = @FundID
                                        AND e.CreatedBy = 'VALCOMM'
                              )
        IF ( @FundID = 'FSIC2' ) 
            BEGIN
                SET @LastValcomDate = '3/29/2013'
            END
        SET @LastValcomExportID = ( SELECT  MAX(e.ExportId)
                                    FROM    [FSFundModel_VALCOMM].[Reporting].Export e
                                    WHERE   e.FundId = @FundID
                                            AND e.CreatedBy = 'VALCOMM'
                                  )
        IF @FundID = 'FSIC2' 
            BEGIN
                SET @LastValcomExportID = ( SELECT  MAX(e.ExportId)
                                            FROM    [FSFundModel_VALCOMM].[Reporting].Export e
                                            WHERE   e.FundId = 'FSIC2'
                                                    AND e.NAVDate = '4/5/2013'
                                          )
            END
           
           
        IF ( @NAVDate IS NULL ) 
            BEGIN
                SELECT  @NAVDate = e.NAVDate
                FROM    [FSFundModel_VALCOMM].[Reporting].Export e
                WHERE   e.ExportID = @ExportID
                        AND FundID = @FundID
            END
           
        IF 1 = 2 
            SELECT  NULL AS column1
           
           --IF ( @ReportName = 'DetailDump' )
           --    BEGIN
           --        SELECT
           --            i.InstrumentId ,
           --            @FundID 'FundID' ,
           --            iss.IssuerName ,
           --            i.InstrName ,
           --            id.InstrClass ,
           --            i.ExtId ,
           --            a.AssetId ,
           --            a.AssQualRat ,
           --            a.Score ,
           --            i.MoodysRating ,
           --            a.GAAPYldPurchPrc ,
           --            a.GAAPYldCurrPrc ,
           --            a.InvWalkGAAPYld ,
           --            a.Cost ,
           --            a.BegTodayAmt ,
           --            a.EndAdjAmt ,
           --            a.AmortCostPctPar ,
           --            a.PurchDate ,
           --            a.SettlementDate ,
           --            a.PurchPrc ,
           --            a.YesterdayPrice ,
           --            a.TodayPrice ,
           --            a.DailyPrcChg ,
           --            a.AmortCost ,
           --            a.MktVal ,
           --            a.UnrealApprec ,
           --            a.UnrealDeprec ,
           --            a.DailyValChgAdjAmt ,
           --            a.DailyValChg ,
           --            i.Floor ,
           --            i.RateType ,
           --            i.BaseRateType ,
           --            i.FixedRate ,
           --            i.MaturityDate ,
           --            a.TotalIntPurchPrc ,
           --            a.TotalIntCurrPurchPrc ,
           --            a.Paydown ,
           --            a.Shares ,
           --            i.PricingVendor ,
           --            i.PricingMethodType ,
           --            a.PropReason ,
           --            a.PropTrack ,
           --            iss.MostRecentData ,
           --            iss.TTMEBITDA ,
           --            iss.IntCov ,
           --i.NotionalVal,
           --            i.FaceLevThruTrn ,
           --            iss.SubSector ,
           --            a.EstDuration ,
           --            a.OID ,
           --            iss.IssuerDescr ,
           --            iss.MarketCap ,
           --            iss.IsPublicComp ,
           --            i.CovLite ,
           --            i.AssetPurpose
           --        FROM
           --            [FSFundModel_VALCOMM].[Reporting].Asset a
           --            INNER JOIN [FSFundModel_VALCOMM].[Reporting].Instrument i
           --            ON i.ExportId = a.ExportId
           --               AND a.InstrumentId = i.InstrumentId
           --            LEFT JOIN [FSFundModel_VALCOMM].[Reporting].FundCreditInstrument fci
           --            ON a.FundCreditInstrumentId = fci.FundCreditInstrumentId
           --               AND a.ExportId = fci.ExportId
           --            INNER JOIN [FSFundModel_VALCOMM].[Reporting].Issuer iss
           --            ON iss.ExportId = a.ExportId
           --               AND i.IssuerId = iss.IssuerId
           --            INNER JOIN [FSFundModel_VALCOMM].[Reporting].InstrumentDefinition id
           --            ON i.InstDefId = id.InstDefId
           --               AND id.ExportId = a.ExportId
           --        WHERE
           --            a.IsActiveEndOfDay = 1
           --            AND a.ExportId = @ExportID
           --        ORDER BY
           --            i.InstrName ASC
           --    END
           
        SELECT  @PriorNAVDate = PrevNAVDate ,
                @cash = CashAdj ,
                @netlevamt = netlevamt ,
                @fundlevyld = Levyld
        FROM    [FSFundModel_VALCOMM].[Reporting].fund f
        WHERE   f.exportid = @exportid
                AND f.CurrNAVDate = @NAVDate
                AND f.FundId = @FundID
           
        DECLARE @results TABLE
            (
              PortfolioID VARCHAR(50) ,
              PortfolioName VARCHAR(50) ,
              InstrClass VARCHAR(100) ,
              InstrName VARCHAR(200) ,
              IssuerName VARCHAR(200) ,
              OrigPurchDate DATETIME ,
              IsQualified VARCHAR(2) ,
              AssetQualityRating INT ,
              PricingDepth VARCHAR(10) ,
              EstimateDuration DECIMAL(38, 12) ,
              Moody_Rating VARCHAR(50) ,
              Portfolio_Moody_Rating VARCHAR(50) ,
              Fund_Moody_Rating VARCHAR(50) ,
              GAAP_Pur VARCHAR(50) ,
              GAAP_Cur VARCHAR(50) ,
              GAAP_Walk VARCHAR(50) ,
              Sum_Par DECIMAL(38, 12) ,
              Amort_Cost_Pct_Par VARCHAR(50) ,
              Weight_Price VARCHAR(50) ,
              PriorNAV_Price DECIMAL(38, 12) ,
              NAVDate_Price DECIMAL(38, 12) ,
              Spread VARCHAR(50) ,
              Ask DECIMAL(38, 12) ,
              Bid DECIMAL(38, 12) ,
              Amort_Cost DECIMAL(38, 12) ,
              MktVal DECIMAL(38, 12) ,
              MktVal_Pct DECIMAL(38, 12) ,
              Net_Unreal_App DECIMAL(38, 12) ,
              Pct_Net_Unreal_Gain VARCHAR(50) ,
              DailyVal_Change DECIMAL(38, 12) ,
              PriceChange_Pct VARCHAR(50) ,
              SecurityID VARCHAR(50) ,
              Sum_Unreal_Dep DECIMAL(38, 12) ,
              Sum_Unreal_App DECIMAL(38, 12) ,
              Prop_Reason VARCHAR(50) ,
              Prop_Track VARCHAR(50) ,
              PricingMethod VARCHAR(50) ,
              TotalInt_Purch_Price DECIMAL(38, 12) ,
              TotalInt_Cur_Price DECIMAL(38, 12) ,
              Sum_OrigCost DECIMAL(38, 12) ,
              Security_Type VARCHAR(50) ,
              Issuer_Name VARCHAR(50) ,
              Lots DECIMAL(38, 12) ,
              Iss_SubSector VARCHAR(100) ,
              Iss_Sector VARCHAR(100) ,
              ExternalID VARCHAR(20) ,
              FocusGroup VARCHAR(5) ,
              FocusReason VARCHAR(100) ,
              portSortOrder INT ,
              instSortOrder INT ,
              priorMoodyRating VARCHAR(5) ,
              InstrumentID INT ,
              Maturity_Date DATETIME ,
              Settle_Disc_prch DECIMAL(38, 12) ,
              Pik_Prch DECIMAL(38, 12) ,
              Coupon_Purch DECIMAL(38, 12)
            )
 
        INSERT  INTO @results
                ( PortfolioID ,
                  PortfolioName ,
                  InstrClass ,
                  InstrName ,
                  IssuerName ,
                  OrigPurchDate ,
                  IsQualified ,
                  AssetQualityRating ,
                  PricingDepth ,
                  EstimateDuration ,
                  Moody_Rating ,
                  Portfolio_Moody_Rating ,
                  Fund_Moody_Rating ,
                  GAAP_Pur ,
                  GAAP_Cur ,
                  GAAP_Walk ,
                  Sum_Par ,
                  Amort_Cost_Pct_Par ,
                  Weight_Price ,
                  PriorNAV_Price ,
                  NAVDate_Price ,
                  Amort_Cost ,
                  MktVal ,
                  Net_Unreal_App ,
                  Pct_Net_Unreal_Gain ,
                  DailyVal_Change ,
                  PriceChange_Pct ,
                  SecurityID ,
                  Sum_Unreal_Dep ,
                  Sum_Unreal_App ,
                  Prop_Reason ,
                  Prop_Track ,
                  PricingMethod ,
                  TotalInt_Purch_Price ,
                  TotalInt_Cur_Price ,
                  Sum_OrigCost ,
                  Security_Type ,
                  Issuer_Name ,
                  Lots ,
                  Iss_SubSector ,
                  Iss_Sector ,
                  ExternalID ,
                  portSortOrder ,
                  instSortOrder ,
                  priorMoodyRating ,
                  InstrumentID ,
                  Maturity_Date ,
                  Settle_Disc_prch ,
                  Pik_Prch ,
                  Coupon_Purch
                         
                         --, FocusReason
                )
                SELECT  a.PortfolioID ,
                        MAX(p.PortfolioName) ,
                        CASE WHEN id.InstrClass = 'Structured Notes'
                             THEN 'Collateralized Securities'
                             ELSE id.InstrClass
                        END ,
                        i.InstrName ,
                        MAX(iss.IssuerName)
            --, a.AssetID
                        ,
                        MIN(a.PurchDate) ,
                        CASE WHEN MAX(CAST(fi.IsQualified AS INT)) = '1'
                             THEN 'Y'
                             ELSE 'N'
                        END ,
                        MAX(a.ASSQualrat) ,
                        '1' ,
                        MAX(a.ESTDuration) ,
                        MAX(i.MoodysRating) ,
                        MAX(p.MoodysRating) ,
                        MAX(f.MoodysRating) ,
                        CASE WHEN SUM(a.Cost) <> '0.0'
                             THEN CAST(CAST(SUM(a.totalIntPurchPrc) AS DECIMAL(38,
                                                              12))
                                  / CAST(SUM(a.Cost) AS DECIMAL(38, 12)) AS VARCHAR)
                             ELSE 'N/A'
                        END ,
                        CASE WHEN SUM(a.MktVal) <> '0.0'
                             THEN CAST(CAST(SUM(a.totalintcurrpurchprc) AS DECIMAL(38,
                                                              12))
                                  / CAST(SUM(a.MktVal) AS DECIMAL(38, 12)) AS VARCHAR)
                             ELSE '0.0'
                        END ,
                        CASE WHEN SUM(a.MktVal) <> '0.0'
                             THEN CAST(CAST(SUM(a.totalIntPurchPrc) AS DECIMAL(38,
                                                              12))
                                  / CAST(SUM(a.MktVal) AS DECIMAL(38, 12)) AS VARCHAR)
                             ELSE 'N/A'
                        END ,
                        SUM(a.EndAdjAmt) ,
                        CASE WHEN SUM(a.AmortCost) <> '0.0'
                             THEN CAST(SUM(a.AmortCost) / SUM(a.EndAdjAmt) AS VARCHAR)
                             ELSE 'N/A'
                        END ,
                        CASE WHEN SUM(a.EndAdjAmt) <> '0.0'
                             THEN CAST(SUM(a.PurchPrc * a.EndAdjAmt)
                                  / SUM(a.EndAdjAmt) AS VARCHAR)
                             ELSE '0.0'
                        END ,
                        MAX(a.YesterdayPrice) ,
                        MAX(a.TodayPrice) ,
                        SUM(a.AmortCost) ,
                        SUM(a.MktVal) ,
                        SUM(a.UnrealDeprec) + SUM(a.unrealapprec) ,
                        CASE WHEN SUM(a.AmortCost) <> '0.0'
                             THEN CAST(( SUM(a.UnrealDeprec)
                                         + SUM(a.unrealapprec) )
                                  / SUM(a.AmortCost) AS VARCHAR)
                             ELSE 'N/A'
                        END ,
                        SUM(a.DailyValChg) ,
                        CASE WHEN MAX(a.YesterdayPrice) > '0.01'
                                  AND MAX(a.TodayPrice) > '0.01'
                             THEN CAST(( ( MAX(a.TodayPrice)
                                           / MAX(a.YesterdayPrice) ) - 1 ) AS VARCHAR)
                             ELSE 'N/A'
                        END ,
                        MAX(i.extid) ,
                        SUM(a.UnrealDeprec) ,
                        SUM(a.UnrealApprec)
            /*, CASE WHEN MAX(CAST(a.IsProprietary AS INT)) = 1
            THEN 'Y'
            ELSE 'N'
            END as Asset_Proprietary*/ ,
                        MAX(ISNULL(a.PropReason, '')) ,
                        MAX(ISNULL(a.PropTrack, '')) ,
                        CASE WHEN MAX(i.PricingMethodType) = 'Cost'
                             THEN 'Cost - to be ' + MAX(i.PricingVendor)
                             ELSE MAX(i.PricingVendor)
                        END ,
                        SUM(a.totalIntPurchPrc) ,
                        SUM(a.totalintcurrpurchprc) ,
                        SUM(a.Cost) ,
                        MAX(id.InstTypeDescr) ,
                        MAX(iss.IssuerName) ,
                        COUNT(InstrName) ,
                        MAX(iss.SubSector) ,
                        MAX(iss.GICSSector) ,
                        MAX(i.ExtId) ,
                        MAX(p.SortOrder) ,
                        MAX(id.sortorder) ,
                        MAX(i.MoodysRating) ,
                        MAX(i.InstrumentId) ,
                        MAX(i.MaturityDate) ,
                        SUM(a.SettDiscPurchPrc) ,
                        SUM(a.PIKPurchAmt) ,
                        SUM(a.CoupRevAnnualPurch)
                FROM    [FSFundModel_VALCOMM].[Reporting].Asset a
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Instrument i ON a.ExportID = i.ExportID
                                                              AND a.InstrumentID = i.InstrumentID
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Issuer iss ON a.ExportID = iss.ExportID
                                                              AND i.IssuerID = iss.IssuerID
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].InstrumentDefinition id ON a.ExportID = id.ExportID
                                                              AND i.InstDefID = id.InstDefID
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].FundIssuer fi ON a.ExportID = fi.ExportID
                                                              AND i.IssuerID = fi.IssuerID
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Portfolio p ON a.ExportId = p.ExportId
                                                              AND A.PortfolioId = p.PortfolioId
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Fund f ON a.ExportID = f.ExportID
                                                              AND F.FundId = @FundID
                WHERE   a.ExportID = @ExportID
                        AND a.IsActiveEndOfDay = 1
                GROUP BY a.PortfolioID ,
                        id.InstrClass ,
                        i.InstrName
            --, a.AssetID
                        ,
                        p.SortOrder ,
                        id.SortOrder
 --ORDER BY
 --    p.SortOrder ,
 --    id.SortOrder
 
 
        INSERT  INTO @results
                ( PortfolioID ,
                  PortfolioName ,
                  InstrClass ,
                  InstrName ,
                  IssuerName ,
                  OrigPurchDate ,
                  IsQualified ,
                  AssetQualityRating ,
                  EstimateDuration ,
                  Moody_Rating ,
                  GAAP_Pur ,
                  GAAP_Cur ,
                  GAAP_Walk ,
                  Sum_Par ,
                  Amort_Cost_Pct_Par ,
                  Weight_Price ,
                  PriorNAV_Price ,
                  NAVDate_Price ,
                  Amort_Cost ,
                  MktVal ,
                  Net_Unreal_App ,
                  Pct_Net_Unreal_Gain ,
                  DailyVal_Change ,
                  PriceChange_Pct ,
                  SecurityID ,
                  Sum_Unreal_Dep ,
                  Sum_Unreal_App ,
                  Prop_Reason ,
                  PricingMethod ,
                  TotalInt_Purch_Price ,
                  TotalInt_Cur_Price ,
                  Sum_OrigCost ,
                  Security_Type ,
                  Issuer_Name ,
                  Lots ,
                  Iss_SubSector ,
                  Iss_Sector ,
                  portSortOrder ,
                  instSortOrder ,
                  InstrumentID
                )
 --DECLARE @Exportid INT
 --SET @Exportid = (SELECT MAX(e.ExportId) FROM FSFundModel_VALCOMM.Reporting.Export e WHERE e.FundId ='FSIC')
                SELECT  MAX(fci.PortfolioId) ,
                        'Unfunded Commitments' AS portfolioName ,
                        'Unfunded Revolving Credit Facilities and Delayed Draws' ,
                        i.InstrName ,
                        MAX(iss.issuername) ,
                        MIN(a.PurchDate) ,
                        CASE WHEN fi.IsQualified = 1 THEN 'Y'
                             ELSE 'N'
                        END ,
                        MAX(fci.AssQualRat) ,
                        NULL ,
                        MAX(i.MoodysRating) ,
                        NULL ,
                        0.0 ,
                        NULL ,
                        SUM(fci.TotalCredit - fci.Drawn) ,
                        ( SUM(fci.AmortCost) / SUM(fci.EndAdjAmt) ) ,
                        CASE WHEN MAX(fci.instrumentid) = 666
                             THEN CAST('90.75' AS DECIMAL(38, 10))
                             ELSE MAX(a.PurchPrc)
                        END ,
                        CASE WHEN MAX(fci.instrumentid) = 666
                             THEN CAST('90.75' AS DECIMAL(38, 10))
                             ELSE MAX(a.TodayPrice)
                        END ,
                        MAX(a.TodayPrice) ,
                        SUM(fci.AmortCost) ,
                        fci.MktVal ,
                        SUM(fci.UnrealApprec) ,
                        NULL ,
                        SUM(fci.DailyValChg) ,
                        NULL ,
                        MAX(i.ExtId) ,
                        SUM(fci.UnrealDeprec) ,
                        SUM(fci.UnrealApprec) ,
                        NULL ,
                        MAX(i.PricingVendor) ,
                        NULL ,
                        NULL ,
                        NULL ,
                        MAX(fci.FundCreditInstrumentType) ,
                        MAX(iss.IssuerName) ,
                        NULL ,
                        MAX(iss.SubSector) ,
                        MAX(iss.GICSSector) ,
                        '7' ,
                        '8' ,
                        fci.FundCreditInstrumentId
                FROM    [FSFundModel_VALCOMM].[Reporting].FundCreditInstrument fci
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Asset a ON a.FundCreditInstrumentId = fci.FundCreditInstrumentId
                       -- AND a.InstrumentId = fci.InstrumentId
                                                              AND fci.ExportId = a.ExportId
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Instrument i ON fci.ExportId = i.ExportId
                                                              AND fci.InstrumentId = i.InstrumentId
            --------------------ADDED-
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Issuer iss ON fci.ExportId = iss.ExportId
                                                              AND i.IssuerId = iss.IssuerId
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].FundIssuer fi ON fci.ExportId = fi.ExportId
                                                              AND i.IssuerId = fi.IssuerId
                                                              AND iss.IssuerId = fi.IssuerId
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].InstrumentDefinition id ON fci.ExportId = id.ExportId
                                                              AND i.InstDefId = id.InstDefId
            
            
            --   ON    a.ExportId = fci.ExportId
            --and a.InstrumentId = fci.InstrumentId
            --AND i.InstrumentId = fci.InstrumentId
                WHERE   fci.ExportId = @ExportID
                        AND a.FundCreditInstrumentId IS NOT NULL
            --AND a.IsActiveEndOfDay = 1
                GROUP BY fci.MktVal ,
                        fci.FundCreditInstrumentId ,
                        fi.IsQualified ,
                        i.InstrName
 --,-- iss.IssuerName
 
        UPDATE  @results
        SET     PriorNAV_Price = 90.75 ,
                Weight_Price = 90.75
        WHERE   InstrName LIKE '%Audio%'
                AND PortfolioName LIKE '%unfund%'
 
 
 --SELECT * FROM  @Results r
 
        DECLARE @totalMktVal DECIMAL(38, 12)
        SET @totalMktVal = ( SELECT SUM(MktVal)
                             FROM   @results
                           )
        UPDATE  r
        SET     r.MktVal_Pct = r.MktVal / @totalMktVal
        FROM    @results r
 
 
 --UPDATE
 -- rslt
 --SET
 -- rslt.AmortCost = fci.
 --FROM
 -- @Results rslt
 
        IF ( @FundID = 'FSIC2' ) 
            BEGIN
                UPDATE  r
                SET     r.priorMoodyRating = inst.Rating ,
                        r.PriorNAV_Price = inst.[3/31 Price]
                FROM    @results r
                        INNER JOIN dbo.IC2331 inst ON inst.ID = r.SecurityID
                WHERE   inst.ID = r.securityid
      
                UPDATE  @results
                SET     PriorNAV_Price = 90.75
                WHERE   SecurityID = 'LX126746'
                        AND PortfolioName LIKE '%Unfunded%'
 
 
 
            END
        IF ( @fundID = 'FSIC' ) 
            BEGIN
                UPDATE  r
                SET     r.priorMoodyRating = inst.moodysrating ,
                        r.PriorNAV_Price = a.TodayPrice
                FROM    @results r
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Instrument inst 
						 ON r.instrumentID = inst.InstrumentId
                        INNER JOIN FSFundModel_VALCOMM.Reporting.Asset a ON inst.ExportId = a.ExportId
                                                              AND inst.InstrumentId = a.InstrumentId
                WHERE   inst.ExportId = @LastValcomExportID
                 --Moodys Rating
                        AND a.IsActiveEndOfDay = 1
 
            END
 
 --SELECT r.instrname, r.priorMoodyRating, r.Moody_Rating FROM @results r  WHERE r.instrname LIKE '%Corner I%'
 
 
        UPDATE  r
        SET     r.pricingDepth = CASE WHEN markit.depth = 'implied' THEN '1'
                                      ELSE markit.depth
                                 END ,
                r.Ask = markit.Offer ,
                r.Bid = markit.bid ,
                r.spread = ( markit.offer - markit.bid )
        FROM    @results r
                INNER JOIN [FSPricing].dbo.Pricing_MarkitLoans_Marks markit ON r.securityID= markit.LoanXID
        WHERE   
				r.securityID = markit.LoanXID
				AND 
                markit.MarkDate = @NAVDate
 
        UPDATE  r
        SET     r.pricingDepth = MB.numQuotes ,
                r.Ask = MB.AskPrice ,
                r.Bid = MB.BidPrice ,
                r.Spread = ( MB.askPrice - MB.bidPrice )
        FROM    @results r
                INNER JOIN [FSPricing].dbo.Pricing_MarkitBonds mb ON r.SecurityID = mb.ISIN
        WHERE   --.instrClass LIKE '%Bond%'
		r.SecurityID = mb.ISIN
		AND

        --        OR r.instrclass LIKE '%Unsecured Corp%'
                mb.PriceDate = @NAVDate
 
				
 
        UPDATE  r
        SET     r.pricingDepth = '1' ,
                r.Ask = thom.Offer ,
                r.Bid = thom.bid ,
                r.Spread = ( thom.offer - thom.bid )
        FROM    @results r
                INNER JOIN FSPricing.dbo.Pricing_ThomsonCLOs thom ON r.SecurityID = thom.FSIdentifier
        WHERE   --r.instrClass LIKE '%Collateralized%'
		 r.SecurityID = thom.FSIdentifier AND 
                 thom.PriceDate = @NAVDate
 
 
        UPDATE  r
        SET     r.pricingdepth = ISNULL(r.pricingdepth, '1')
        FROM    @results r
 
        UPDATE  r
        SET     r.pricingdepth = '1'
        FROM    @results r
        WHERE   r.pricingdepth = '0'
 
 
 
        UPDATE  r
        SET     r.spread = ISNULL(r.spread, r.ask - r.bid)
        FROM    @results r
        WHERE   r.instrclass NOT LIKE '%Equity%'
 
 
 
 
 
 
 
 
        IF ( @ReportName = 'FundTotals' ) 
            BEGIN
                SELECT  f.GAAPYldCurrPrc ,
                        f.PurchPrc ,
                        f.MoodysRating ,
                        f.TodayPrice ,
                        f.EndAdjAmt ,
                        f.AmortCost ,
                        f.MktVal
                FROM    [FSFundModel_VALCOMM].[Reporting].Fund f
                WHERE   f.ExportId = @ExportId
            END
 
 
 
 
 
 
 
 
 
        IF ( @fundID = 'FSIC' ) 
            BEGIN
      
                UPDATE  r
                SET     r.priorMoodyRating = inst.moodysrating
                FROM    @results r
                        INNER JOIN [FSFundModel_VALCOMM].[Reporting].Instrument inst ON r.instrumentID = inst.InstrumentId
                WHERE   inst.ExportId = @LastValcomExportID
      --Moodys Rating
 
            END
 
 
        IF ( @ReportName = 'Top_20_Valcomm' ) 
            BEGIN
                DECLARE @Top20Issuer TABLE
                    (
                      IssuerID INT ,
                      MktVal DECIMAL(38, 12) ,
                      IssuerName VARCHAR(500)
                    )
 
                INSERT  INTO @Top20Issuer
                        ( IssuerID ,
                          MktVal ,
                          IssuerName
                        )
                        SELECT TOP 20
                                fi.IssuerID ,
                                fi.MktValLT ,
                                i.IssuerName
                        FROM    [FSFundModel_VALCOMM].[Reporting].FundIssuer fi
                                INNER JOIN [FSFundModel_VALCOMM].[Reporting].Issuer i ON fi.ExportID = i.ExportID
                                                              AND fi.IssuerID = i.IssuerID
                        WHERE   fi.ExportID = @ExportID
                                AND fi.FundId = @FundID
                        ORDER BY fi.MktValLT DESC
 
 
                SELECT  r.IssuerName ,
                        r.InstrName ,
                        r.GAAP_Cur ,
                        r.Weight_Price ,
                        r.OrigPurchDate ,
                        r.PricingDepth ,
                        r.Moody_Rating ,
                        CASE WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS PriorNAVDate_Price ,
                        ISNULL(r.Spread, 'N/A') AS Spread ,
                        r.NAVDate_Price AS NAVDate_Price ,
                        r.Sum_Par ,
                        r.Amort_Cost ,
                        i.MktVal AS Issuer_MktVal ,
                        r.MktVal AS Instrument_MktVal ,
                        CASE WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS PrevQPrice ,
                        CASE WHEN r.bid IS NULL THEN 0.0
                             ELSE r.bid
                        END AS Bid ,
                        CASE WHEN r.ask IS NULL THEN 0.0
                             ELSE r.ask
                        END AS ask,
						r.AssetQualityRating
            --  then r.prchPrice
            -- else r.prevQuarterPrice
            --end as PrevQPrice
                FROM    @results r
                        INNER JOIN @Top20Issuer i ON r.Issuer_Name = i.IssuerName
                WHERE   r.PortfolioName <> 'Unfunded Commitments'
            -- fix to remove unfunded in t20
                ORDER BY i.MktVal DESC ,
                        instSortOrder ,
                        r.instrname
            END
 
        IF ( @ReportName LIKE 'ValComm_PortfolioTotal' ) 
            BEGIN
      
                SELECT  mid.FS_PortAbbrev AS PortfolioID ,
                        pf.PortfolioName ,
                        pf.[GAAPYldCurrPrc] ,
                        pf.[PurchPrc] ,
                        pf.[MoodysRating] ,
                        pf.[TodayPrice] ,
                        pf.[EndAdjAmt] ,
                        pf.[AmortCost] ,
                        pf.[MktVal] ,
                        pPreviousValcom.TodayPrice AS PrevPrice,
						pf.[PortAllocPct] 
                FROM    [FSFundModel_VALCOMM].Reporting.Portfolio pF
                        INNER JOIN dbo.mapSSB_PortID mID ON mID.ART_PortfolioID = pF.PortfolioId ,
                        [FSFundModel_VALCOMM].Reporting.Portfolio pPreviousValcom
                WHERE   pPreviousValcom.PortfolioId = pf.PortfolioId
                        AND pf.ExportID = @ExportID
                        AND pf.FundID = @FundID
                        AND pPreviousValcom.ExportId = @LastValcomExportID
 
            END
 
 ------------------------------------
 -- Valuation Committee Summary
 ------------------------------------
 
        IF ( @ReportName LIKE 'ValComm_Summary%' ) 
            BEGIN
                IF ( @ReportName <> 'ValComm_Summary_Focus' ) 
                    BEGIN
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                                   THEN r.instrname + '(6)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.pricingmethod LIKE '%VRC%'
                                                   THEN r.instrname + '(7)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.Issuer_Name IN (
                                                        SELECT DISTINCT
                                                              Issuer_Name
                                                        FROM  @results r
                                                        WHERE r.pricingmethod = 'VRC' )
                                                        AND r.pricingmethod <> 'VRC'
                                                   THEN r.instrname + '(8)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
                        UPDATE  r
                        SET     r.instrname = CASE WHEN r.AssetQualityRating IN (
                                                        4, 5 )
                                                   THEN r.instrname + '(9)'
                                                   ELSE r.instrname
                                              END
                        FROM    @results r
           
           
         
                    END 
      
      
                UPDATE  @results
                SET     PriorNAV_Price = 90.57
                WHERE   securityID = 'LX126746'
      
      
                UPDATE  r
                SET     r.focusreason = CASE WHEN r.pricingmethod LIKE '%Cost%'
                                             THEN 'Asset Marked at Cost'
                                             ELSE ''
                                        END
                FROM    @results r
      
      
                UPDATE  r
                SET     r.FocusGroup = CASE WHEN r.AssetQualityRating IN ( '4',
                                                              '5' ) THEN 'Yes'
                                            WHEN r.PricingMethod LIKE '%VRC%'
                                            THEN 'Yes'
                                            WHEN r.PricingMethod LIKE '%Cost%'
                                            THEN 'Yes'
                                            ELSE 'No'
                                       END
                FROM    @results r
      
                UPDATE  r
                SET     r.FocusReason = CASE WHEN r.FocusReason <> 'Asset marked at Cost'
                                             THEN CASE WHEN r.PricingMethod LIKE '%VRC%'
                                                       THEN CASE
                                                              WHEN r.AssetQualityRating = '4'
                                                              THEN 'Level 4 Asset / VRC'
                                                              WHEN r.AssetQualityRating = '5'
                                                              THEN 'Level 5 Asset / VRC'
                                                              ELSE 'VRC'
                                                            END
                                                       ELSE CASE
                                                              WHEN r.assetQualityRating > 3
                                                              THEN 'Level '
                                                              + CAST(r.assetQualityRating AS VARCHAR)
                                                              + ' Asset'
                                                              ELSE ''
                                                            END
                                                  END
                                             ELSE r.focusreason
                                        END
                FROM    @results r
      --UPDATE
      --     r
      -- SET
      --     r.FocusReason = CASE WHEN r.FocusReason <> 'Asset marked at Cost'
      --                         then case  WHEN r.PricingMethod LIKE '%VRC%'
      --                                    THEN CASE WHEN r.focusreason <> ''
      --                                              THEN r.focusreason + ' / '
      --                                                   + 'VRC'
      --                                              ELSE 'VRC'
      --                                         END
      --                                    --ELSE r.focusreason
      --                          end
      --                ELSE r.focusreason
      --    end
      -- FROM
      --     @results r
      --UPDATE
      --    r
      --SET
      --    r.focusreason = CASE WHEN  r.FocusReason <> 'Asset marked at Cost' and r.PricingMethod LIKE '%VRC%'
      --                         THEN CASE WHEN r.focusreason = '' THEN 'VRC'
      --                                   ELSE r.focusreason + ' / ' + 'VRC'
      
      --                              END
      --                         ELSE r.focusreason
      --                    END
      --FROM
      --    @results r
      
                IF ( @FundID <> 'FSIC' ) 
                    BEGIN
                        UPDATE  @results
                        SET     FocusReason = CASE WHEN ABS(( CAST(NAVDate_Price AS DECIMAL(38,
                                                              10))
                                                              / NULLIF(CAST(PriorNAV_Price AS DECIMAL(38,
                                                              10)), 0) )
                                                            - 1.0000) > 0.0800
                                                   THEN CASE WHEN FocusReason = ''
                                                             THEN 'Price change > 8% QoQ'
                                                             ELSE FocusReason
                                                              + ' / Price change > 8% QoQ'
                                                        END
                                                   ELSE FocusReason
                                              END
           
           
           
                        UPDATE  @results
                        SET     FocusGroup = CASE WHEN ( LEN(FocusReason) > 3 )
                                                  THEN 'Yes'
                                                  ELSE 'No'
                                             END
      
             
           
                        DECLARE @temp TABLE
                            (
                              purchPrice DECIMAL(38, 10) ,
                              Par DECIMAL(38, 10) ,
                              Portfolio VARCHAR(MAX) ,
                              InstID VARCHAR(MAX) ,
                              weightPc DECIMAL(38, 10)
                            )
                        INSERT  INTO @temp
                                ( purchPrice ,
                                  Par ,
                                  Portfolio ,
                                  InstID
                                )
                                SELECT  CASE WHEN a.PurchDate < '3/31/2013'
                                             THEN ic2.[3/31 Price]
                                             ELSE a.PurchPrc
                                        END ,
                                        a.EndAdjAmt ,
                                        a.PortfolioId ,
                                        i.ExtId
                                FROM    FSFundModel_VALCOMM.Reporting.Asset a
                                        INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i ON a.ExportId = i.ExportId
                                                              AND a.InstrumentId = i.InstrumentId
                                        INNER JOIN FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci ON a.ExportId = Fci.ExportId
                                                              AND fci.InstrumentId = a.InstrumentId
                                                              AND fci.InstrumentId = i.InstrumentId
                                        INNER JOIN dbo.IC2331 ic2 ON i.ExtId = ic2.ID
                                WHERE   a.ExportId = @ExportID
                                        AND a.IsActiveEndOfDay = 1
 
 
 
                        DECLARE @tmp2 TABLE
                            (
                              purchPrice DECIMAL(38, 10) ,
                              Portfolio VARCHAR(MAX) ,
                              InstID VARCHAR(MAX)
                            )
                        INSERT  INTO @tmp2
                                SELECT  SUM(t.Par * t.purchPrice) / SUM(t.par) ,
                                        t.Portfolio ,
                                        t.InstID
                                FROM    @temp t
                                GROUP BY t.Portfolio ,
                                        t.InstID
 
                        UPDATE  @results
                        SET     PriorNAV_Price = t.purchPrice
                        FROM    @tmp2 t
                        WHERE   ( t.Portfolio = PortfolioID )
                                AND t.InstID = SecurityID
 --UPDATE @temp
 --SET weightPc =
 
 --(SELECT
 
 --FROM @temp t
 -- GROUP BY t.Portfolio, t.InstID)
 --WHERE
 
 --INNER JOIN FSFundModel_VALCOMM.Reporting.Asset a
 --ON a.InstrumentId = r.InstrumentID
 --AND a.PortfolioId = r.PortfolioID
 
 --INNER JOIN  FSFundModel_VALCOMM.Reporting.Instrument i
 --ON a.ExportId = i.ExportId
 --AND r.InstrumentID = i.InstrumentId
 --AND a.InstrumentId = i.InstrumentId
 
 --INNER JOIN dbo.IC2331 ic2 ON ic2.[ID] = r.securityID
 --WHERE r.origpurchdate <= '3/28/2013'
 
                        --UPDATE  @results
                        --SET     FocusReason = CASE WHEN ABS(( CAST(NAVDate_Price AS DECIMAL(38,
                        --                                      10))
                        --                                      / NULLIF(CAST(PriorNAV_Price AS DECIMAL(38,
                        --                                      10)), 0) )
                        --                                    - 1.0000) > 0.0800
                        --                           THEN CASE WHEN FocusReason = ''
                        --                                     THEN 'Price change > 8% QoQ'
                        --                                     ELSE FocusReason
                        --                                      + ' / Price change > 8% QoQ'
                        --                                END
                        --                           ELSE FocusReason
                        --                      END
                        --FROM    @results
 
                        UPDATE  @results
                        SET     FocusGroup = 'Yes'
                        WHERE   FocusReason <> ''
                    
                UPDATE  @results
                SET     PriorNAV_Price = CASE WHEN PriorNAV_Price IS NULL
                                              THEN PriorNAV_Price
                                              WHEN OrigPurchDate > @LastValcomDate
                                              THEN Weight_Price
                                              ELSE CASE WHEN SecurityID = 'LX126746'

                                                        THEN 90.75
                                                        ELSE PriorNAV_Price
                                                   END
                                         END
              
 END
 
                UPDATE  r
                SET     r.FocusGroup = 'Yes' ,
                        r.FocusReason = 'Other assets priced by VRC'
                FROM    @results r
                WHERE   r.Issuer_Name IN ( SELECT DISTINCT
                                                    Issuer_Name
                                           FROM     @results r
                                           WHERE    r.PricingMethod = 'VRC' )
                        AND r.FocusGroup = 'No'
 
 
 
 
 
 
                SELECT  mID.FS_PortAbbrev AS PortfolioID ,
                        r.PortfolioName ,
                        r.InstrClass ,
                        r.InstrName ,
                        CASE WHEN r.focusgroup = 'Yes' THEN 'TRUE'
                             ELSE 'FALSE'
                        END ,
                        r.OrigPurchDate ,
                        r.SecurityID AS Security_Identifier ,
                        r.IsQualified AS Is_Qualified ,
                        r.AssetQualityRating AS Asset_Quality_Rating ,
                        CASE WHEN r.PricingDepth IS NULL THEN '1'
                             ELSE r.pricingdepth
                        END AS Depth ,
                        r.EstimateDuration AS Estimated_Duration ,
                        r.Moody_Rating AS Moody_Rating ,
                        CASE WHEN r.Spread IS NULL THEN 'N/A'
                             ELSE CAST(r.Spread AS VARCHAR)
                        END AS Spread ,
                        r.GAAP_Pur AS GAAP_Yield_At_Purchase_Price ,
                        r.GAAP_Cur AS GAAP_Yield_At_Current_Price ,
                        r.GAAP_Walk AS Walk_In_GAAP_Yield ,
                        r.sum_par AS Sum_Of_Adj_Par ,
            --CASE WHEN InstrClass = 'Equity/Other'
            --          AND ( r.GAAP_Pur = 'N/A' )--r.GAAP_Pur = '0.000000'
            --          THEN '0'
            --     WHEN InstrClass = 'Equity/Other'
            --          AND CAST(r.GAAP_Pur AS DECIMAL(38, 12)) = '0.0' THEN '0'
            --     ELSE CAST(r.Sum_Par AS VARCHAR)
            --END AS Sum_Of_Adj_Par ,
                        r.Amort_Cost_Pct_Par AS Amortized_Cost_Pct_of_Par ,
                        r.Weight_Price AS Weighted_Avg_Price ,
                        r.PriorNAV_Price AS PriorNAVDate_Price
            -- fix with dynamic SQL later
                        ,
                        r.NAVDate_Price AS NAVDate_Price
            -- fix with dynamic SQL later
                        ,
                        r.Amort_Cost AS Sum_Of_Amort_Cost ,
                        r.MktVal AS Sum_Of_Mkt_Val ,
                        r.MktVal_Pct AS Percent_of_MarketValue ,
                        r.Net_Unreal_App AS Net_Unrealized_Appreciation ,
                        r.Pct_Net_Unreal_Gain AS Pct_Net_Unreal_Gain ,
                        r.DailyVal_Change AS Sum_Of_Daily_Value_Change ,
                        r.PriceChange_Pct AS Price_Chg_Pct ,
                        r.Sum_Unreal_Dep AS Sum_Of_Unreal_Dep ,
                        r.Sum_Unreal_App AS Sum_Of_Unreal_App ,
                        r.Prop_Reason AS Proprietary_Reason ,
                        r.PricingMethod AS Pricing_Method_Type ,
                        r.TotalInt_Purch_Price AS Interest_purchase_price_method ,
                        r.TotalInt_Cur_Price AS Interest_current_price_method ,
                        r.Sum_OrigCost AS Sum_Of_Original_Cost ,
                        r.Security_Type ,
                        r.FocusGroup ,
                        r.FocusReason ,
                        r.portSortOrder ,
                        r.instSortOrder ,
                        r.Issuer_Name ,
                        CASE WHEN r.PriorNAV_Price IS NULL
                             THEN r.PriorNAV_Price
                             WHEN r.OrigPurchDate > @LastValcomDate
                             THEN r.Weight_Price
                             ELSE r.PriorNAV_Price
                        END AS prevQuarterPrice ,
                        CASE WHEN r.Bid IS NULL THEN 'N/A'
                             ELSE CAST(r.Bid AS VARCHAR)
                        END AS Bid ,
                        CASE WHEN r.Ask IS NULL THEN 'N/A'
                             ELSE CAST(r.Ask AS VARCHAR)
                        END AS Ask ,
                        CASE WHEN r.pricingmethod LIKE '%Cost%'
                             THEN 'Investment Memorandum'
                             WHEN r.pricingMethod LIKE '%VRC%'
                             THEN 'Valuation Report'
                             WHEN r.pricingMethod LIKE '%Markit%'
                             THEN 'Markit Screenshot'
                        END AS MaterialProvided ,
                        dbo.GtMoodysChange(r.moody_rating, r.priormoodyrating) AS moodyChange ,
                        r.priorMoodyRating ,
                        r.iss_Subsector ,
                        r.prop_Reason ,
                        r.Prop_Track ,
                        r.Maturity_Date ,
                        r.Settle_Disc_prch ,
                        r.Pik_Prch ,
                        r.Coupon_Purch
                FROM    @results r
                        INNER JOIN dbo.mapSSB_PortID mID ON mID.ART_PortfolioID = r.PortfolioId
            
            --WHERE   r.FocusGroup = CASE WHEN @ReportName = 'ValComm_Summary_Focus'
            --                                        THEN 'Yes'
            --                                        ELSE r.FocusGroup
            --                                   END
                WHERE   r.FocusGroup = CASE WHEN @ReportName = 'ValComm_Summary_Focus'
                                            THEN 'Yes'
                                            ELSE r.FocusGroup
                                       END
            --AND r.Prop_Track = CASE WHEN @ReportName like 'ValComm_Summary_Prop%' THEN 'Proprietary'
            -- ELSE r.Prop_Track
            -- END
                ORDER BY CASE WHEN @ReportName = 'ValComm_Summary'
                                   OR @reportName = 'Valcomm_Summary_Prop'
                              THEN r.portSortOrder
                         END ASC ,
            --CASE WHEN @ReportName = 'ValComm_Summary_Prop_GSO' THEN r.prop_Reason END ASC,
                        r.instsortorder ,
                        r.InstrName ASC
 
            END
 
    END
 
 
 
 
 -- SELECT * FROM FSFundModel_VALCOMM.Reporting.FundCreditInstrument fci WHERE fci.ExportId = (SELECT MAX(e.ExportId) FROM FSFundModel_VALCOMM.Reporting.Export e WHERE e.FundId ='FSIC')
GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Valcom', 'SCHEMA', N'dbo', 'PROCEDURE', N'DailyReporting_ValComm_Backup', NULL, NULL
GO
